if exists (select 1 from sysobjects where name = 'get_personen' and type = 'P')
begin
   drop procedure get_personen
   print 'Procedure: get_personen deleted ...'
end
go
create procedure get_personen(
  @mandid            char(2) = 'li',
  @prsid             int      = 0,
  @typ               int      = 1 --0: alle, 1:Stammpersonen, 2:Zusatzpersonen
)
as
begin
  set nocount on
  
  create table #prs
  (
       prsid     int,
       persontyp int
  )
  
  if @typ = 0
  begin
  
  insert #prs
  select prsid = PrsId,
         persontyp = PersonTyp
    from Person
   where PrsId = @prsid or @prsid = 0
  
  -- define nicht angestellte
  update #prs
     set persontyp = 3
    from #prs p
   inner join Arbeitspensum a
      on a.PrsID = p.prsid
     and a.Bis = (select max(a2.Bis) from Arbeitspensum a2 where a.PrsID = a2.PrsID)
   where (a.Bis <= GETDATE() or a.Von > GETDATE())
      
  
  
  end
  else if @typ = 1
  begin
  
  insert #prs
  select distinct
         prsid = Arbeitspensum.PrsID,
         persontyp = PersonTyp
    from Arbeitspensum, Person
   where (Arbeitspensum.PrsID = @prsid or @prsid = 0)
     --and Von <= getdate() 
     and Bis >= GETDATE()
     and Person.PrsId = Arbeitspensum.PrsID
     and Person.PersonTyp = 1
  
  end
  else if @typ = 2
  begin
  
  insert #prs
  select prsid = PrsId,
         persontyp = PersonTyp
    from Person
   where PersonTyp = 2
   
  end
  
  select prsid = p.PrsId,
         vorname = p.Vorname,
         nachname = p.Nachname,
         strasse = isnull(p.Strasse,''),
         stnr = isnull(p.Strassennr,0),
         postfach = isnull(p.Postfach,''),
         plz = isnull(p.PLZ,''),
         ort = isnull(p.Ort,''),
         telp = isnull(p.TelnrP,''),
         teln = isnull(p.TelnrN,''),
         email = isnull(p.Email,''),
         typ = #prs.persontyp,
         anzeigename = p.Vorname + ' ' + p.Nachname
    from Person p
   inner join #prs
      on p.PrsId = #prs.prsid
   where (p.mandid = @mandid or @mandid = '')
     and (p.PrsId = @prsid or @prsid = 0)
     and (p.PersonTyp = @typ or @typ = 0)
     
     
     --select * from arbeitspensum,person where person.prsid = arbeitspensum.prsid
     --select * from person

end
go
print 'Procedure: get_personen done ...'
go

grant exec on get_personen to prsadmins with grant option
go
grant exec on get_personen to prsusers
go

